package com.dao.impl;

import com.dao.MedalInfoDAO;
import com.entity.MedalInfo;
import util.DBUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MedalInfoDAOImpl implements MedalInfoDAO {
    @Override
    public int insert(MedalInfo entity) throws SQLException {
        String sql = "INSERT INTO medal_info(medal_id,se_id,participant_id,medal_desc,medal_grade,medal_date) values(?,?,?,?,?,?)";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, entity.getMedalId());
            pstmt.setObject(2,entity.getSeId());
            pstmt.setObject(3,entity.getParticipantId());
            pstmt.setObject(4,entity.getMedalDesc());
            pstmt.setObject(5,entity.getMedalGrade());
            pstmt.setObject(6,entity.getMedalDate());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int updateById(MedalInfo entity) throws SQLException {
        String sql = "update country_info set se_id=?,participant_id=?,medal_desc=?,medal_grade=?,medal_date=? where medal_id=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(6, entity.getMedalId());
            pstmt.setObject(1,entity.getSeId());
            pstmt.setObject(2,entity.getParticipantId());
            pstmt.setObject(3,entity.getMedalDesc());
            pstmt.setObject(4,entity.getMedalGrade());
            pstmt.setObject(5,entity.getMedalDate());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from Medal_info  where medal_id=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, id);
            return pstmt.executeUpdate();
        }
    }

    @Override
    public MedalInfo selectById(Serializable id) throws SQLException {
        String sql = "select * from medal_info  where medal_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                MedalInfo entity = new MedalInfo();
                entity.setMedalId(rs.getInt("medal_id"));
                //如果该字段可能为空
                entity.setSeId(rs.getInt("se_id"));
                entity.setParticipantId(rs.getInt("participant_id"));
                entity.setMedalDesc(rs.getInt("medal_desc"));
                entity.setMedalGrade(rs.getString("medal_grade"));
                entity.setMedalDate(rs.getString("medal_date"));
                return entity;
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public List<MedalInfo> select(int index, int len) throws SQLException {
        String sql = "select * from medal_info  order by medal_id limit ?,?";//只要是返回多条记录,一定记得+order by子句
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, index);
            pstmt.setObject(2, len);
            rs = pstmt.executeQuery();
            List<MedalInfo> list = new ArrayList<>();
            while (rs.next()) {
                MedalInfo entity = new MedalInfo();
                entity.setMedalId(rs.getInt("medal_id"));
                //如果该字段可能为空
                entity.setSeId(rs.getInt("se_id"));
                entity.setParticipantId(rs.getInt("participant_id"));
                entity.setMedalDesc(rs.getInt("medal_desc"));
                entity.setMedalGrade(rs.getString("medal_grade"));
                entity.setMedalDate(rs.getString("medal_date"));
                list.add(entity);
            }
            return list;
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from medal_info";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return rs.getLong(1);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }
}
